<html>
<script>
	function getSections() {
		var name = document.getElementById('faculty_name').value;
		window.location.href = 'report2b.jsp?faculty_name=' + name;
	}
</script>
<body>
	<table border="1">
		<tr>
			<td valign="top">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="index.html" />
			</td>
			<td><%@ page language="java"
					import="java.sql.*,java.util.*,java.io.*,java.text.*,data.*"%>
				<%@ page
					import="javax.sql.rowset.CachedRowSet,com.sun.rowset.CachedRowSetImpl;"%>
				<p>Assist a professor X in scheduling a review session for a
					section Y offered in the current quarter during the time period
					from B to E</p> <%
 	SimpleDateFormat df = new SimpleDateFormat("MM/dd/yy");
 	List<String> availableSlots = null;
 	Connection conn = null;
 	try {
 		// Load Oracle Driver class file
 		DriverManager
 				.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());

 		// Make a connection to the MS SQL database "cse132b"
 		conn = DriverManager
 				.getConnection(
 						"jdbc:sqlserver://localhost\\SQLEXPRESS;databaseName=cse132b",
 						"cse132b", "cse132b");

 		// Get current quarter
 		InputStream in = application
 				.getResourceAsStream("settings.properties");
 		Properties p = new Properties();
 		p.load(in);
 		String quarter = p.getProperty("quarter");
 		int year = Integer.parseInt(p.getProperty("year"));
 		java.util.Date quarterStart = df.parse(p.getProperty("start"));
 		java.util.Date quarterEnd = df.parse(p.getProperty("end"));

 		// Load sections by professor from database

 		String action = request.getParameter("action");
 		// Check if an query is requested
 		if (action != null && action.equals("submit")) {
 			// Begin transaction
 			conn.setAutoCommit(false);

 			// Set search period
 			java.util.Date startDate = df.parse(request
 					.getParameter("start_date"));
 			java.util.Date endDate = df.parse(request
 					.getParameter("end_date"));
 			if (startDate.before(quarterStart)) {
 				startDate = quarterStart;
 			}
 			if (endDate.after(quarterEnd)) {
 				endDate = quarterEnd;
 			}

 			// Get schedule of students
 			PreparedStatement pstmt = conn
 					.prepareStatement("SELECT DISTINCT start_time, end_time, weekday FROM enroll e, meeting m, section s, class c "
 							+ "WHERE e.section_id = m.section_id AND s.section_id = m.section_id AND s.class_id = c.class_id AND "
 							+ "c.quarter = ? AND c.year = ? AND e.student_id IN (SELECT student_id FROM enroll WHERE section_id = ?)");
 			pstmt.setString(1, quarter);
 			pstmt.setInt(2, year);
 			pstmt.setInt(3, Integer.parseInt(request
 					.getParameter("section_id")));

 			ResultSet rsMeeting = pstmt.executeQuery();

 			// day starts from 0 (Monday) to 4 (Friday)
 			// time starts from 0 (8 am) to 11 (7 pm)
 			boolean[][] slotsTaken = new boolean[5][12];

 			while (rsMeeting.next()) {
 				String weekdays = rsMeeting.getString("weekday");
 				int startHour = Integer.parseInt(rsMeeting.getString(
 						"start_time").split(":")[0]);
 				String[] tmp = rsMeeting.getString("end_time").split(
 						":");
 				int endHour = Integer.parseInt(tmp[0]);
 				if (Integer.parseInt(tmp[1]) == 0)
 					endHour--;
 				for (String day : weekdays.split(" ")) {
 					int d = -1;
 					if (day.equals("M"))
 						d = 0;
 					else if (day.equals("Tu"))
 						d = 1;
 					else if (day.equals("W"))
 						d = 2;
 					else if (day.equals("Th"))
 						d = 3;
 					else if (day.equals("F"))
 						d = 4;
 					if (d >= 0) {
 						for (int hour = startHour; hour <= endHour; hour++) {
 							slotsTaken[d][hour - 8] = true;
 						}
 					}
 				}
 			}

 			availableSlots = new ArrayList<String>();
 			java.util.Date curr = startDate;
 			// A day after end date
 			endDate = Util.increment(endDate);

 			while (!curr.equals(endDate)) {
 				if (Util.isWeekday(curr)) {
 					for (int hour = 8; hour < 20; hour++) {
 						if (!slotsTaken[Util.getDayOfWeek(curr) - 2][hour - 8]) {
 							availableSlots.add(Util.slot(curr, hour));
 						}
 					}
 				}
 				curr = Util.increment(curr);
 			}

 			conn.setAutoCommit(true);
 		}

 		// Sections given by professors in current quarter
 		Map<String, List<Section>> sectionsByProfessor = new TreeMap<String, List<Section>>();

 		// Create the statement
 		Statement statement = conn.createStatement();

 		// Get all professors and the sections they teach in current quarter
 		PreparedStatement pstmt = conn
 				.prepareStatement("SELECT faculty_name, section_id, title, course_number FROM class c, section s WHERE "
 						+ "c.class_id = s.class_id AND c.quarter = ? AND c.year = ?");
 		pstmt.setString(1, quarter);
 		pstmt.setInt(2, year);
 		ResultSet rsSection = pstmt.executeQuery();
 		while (rsSection.next()) {
 			String professor = rsSection.getString("faculty_name");
 			if (!sectionsByProfessor.containsKey(professor)) {
 				sectionsByProfessor.put(professor,
 						new LinkedList<Section>());
 			}
 			sectionsByProfessor.get(professor).add(
 					new Section(rsSection.getString("title"), rsSection
 							.getString("course_number"), rsSection
 							.getInt("section_id")));
 		}
 %>
				<table border="1">
					<tr>
						<th>Professor</th>
						<th>Section</th>
						<th>Start (mm/dd/yy)</th>
						<th>End (mm/dd/yy)</th>
					</tr>
					<tr>
						<form action="report2b.jsp" method="POST">
							<input type="hidden" value="submit" name="action">
							<td><select id="faculty_name" name="faculty_name"
								style="width: 150px" onchange="getSections()">
									<option></option>
									<%
										String professor = request.getParameter("faculty_name");
											for (String name : sectionsByProfessor.keySet()) {
									%>
									<option value="<%=name%>"
										<%if (name.equals(professor))
						out.print("selected='true'");%>>
										<%=name%>
									</option>
									<%
										}
									%>
							</select></td>
							<td><select name="section_id" style="width: 250px">
									<%
										if (professor != null && !professor.isEmpty()) {
												for (Section s : sectionsByProfessor.get(professor)) {
									%>
									<option value="<%=s.getId()%>"><%=s%></option>
									<%
										}
											}
									%>
							</select></td>
							<td><input name="start_date" size="10"></td>
							<td><input name="end_date" size="10"></td>
							<td><input type="submit" value="submit"></td>
						</form>
					</tr>
				</table> <%
 	if (availableSlots != null) {
 %>
				<ul>
					<%
						for (String slot : availableSlots) {
					%><li><%=slot%></li>
					<%
						}
					%>
				</ul> <%
 	}
 	} catch (SQLException e) {
 		System.out.println(e.getMessage());
 	} catch (Exception e) {
 		System.out.println(e.getMessage());
 	}
 %></td>
		</tr>
	</table>
</body>
</html>